package gen.tools.query;

import gen.tools.Widgets;
import gen.tools.structures.DepMemphis;
import gen.tools.structures.DepMemphisDetail;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class QueryDepRecordMemphis extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public QueryDepRecordMemphis() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		final Widgets wid = new Widgets();
		request.setCharacterEncoding(wid.UIcodeSet);
		response.setCharacterEncoding(wid.UIcodeSet);
		response.setContentType("application/json");
		
		final String strSQL="SELECT t1.depMemphisSN,t1.depType,t1.svnUSR,t1.svnURL,t1.cDT,t2.depMemphisDetailId,t2.isDep,t2.squadId,t2.fileName,t2.fileVer,t2.relativePath,t2.isSuccess,t2.errMsg,t2.isAssume,t3.empName FROM depmemphis t1,depmemphisdetail t2,emps t3 WHERE t1.depMemphisSN=t2.depMemphisSN AND t1.creatorId=t3.empId ORDER BY t1.depMemphisSN DESC,t2.depMemphisDetailId ASC";
		
		ArrayList<DepMemphis> dms = new ArrayList<DepMemphis>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int preSN = -1;
		try {
			conn = wid.getConn();
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			
			while ( rs.next() ) {
				if( rs.getInt("depMemphisSN") == preSN ) {
					DepMemphisDetail dmd = new DepMemphisDetail();
					dms.get(dms.size()-1).setDetailSize( ( dms.get(dms.size()-1).getDetailSize()+1  ) );
					dmd.setDepMemphisDetailId(rs.getInt("depMemphisDetailId"));	
					dmd.setSquadId(rs.getString("squadId"));
					dmd.setFileName(rs.getString("fileName"));
					dmd.setFileVer(rs.getInt("fileVer"));
					dmd.setRelativePath(rs.getString("relativePath"));
					dmd.setSuccess("t".equalsIgnoreCase( rs.getString("isSuccess") ));
					dmd.setErrMsg( rs.getString("errMsg"));
					dmd.setDep( "t".equalsIgnoreCase( rs.getString("isDep") ) );
					dms.get(dms.size()-1).getDmds().add(dmd);
				} else {	//表示是新的一筆記錄
					preSN = rs.getInt("depMemphisSN");
					DepMemphis dm = new DepMemphis();
					dm.setDepMemphisSN(rs.getInt("depMemphisSN"));
					dm.setDepMemphisDetailId(1);	//為了前端處理方便，單頭直接存第一筆的資料
					dm.setDepType(rs.getInt("depType"));
					dm.setSvnURL(rs.getString("svnURL"));
					dm.setSvnUSR(rs.getString("svnUSR"));
					dm.setSquadId(rs.getString("squadId"));
					dm.setFileName(rs.getString("fileName"));
					dm.setFileVer(rs.getInt("fileVer"));
					dm.setDep( "t".equalsIgnoreCase( rs.getString("isDep") ) );
					dm.setDetailSize(1);
					dm.setAssume("t".equalsIgnoreCase( rs.getString("isAssume") ));
					dm.setRelativePath(rs.getString("relativePath"));
					dm.setSuccess("t".equalsIgnoreCase( rs.getString("isSuccess") ));
					dm.setErrMsg( rs.getString("errMsg"));
					dm.setcDT(wid.changeTimeFormatUI(rs.getTimestamp("cDT")));
					dm.setCreator(rs.getString("empName"));
					dms.add(dm);
				}
				
			}
		} catch (Exception e) {
			wid.errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}
			} catch (SQLException e) {e.printStackTrace();}
		}
		
		
		response.getWriter().write( new Gson().toJson(dms) );
	}

}
